package queries;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import common.CheckBoxObject;

import entitys.Customer;
import entitys.MarketingPattern;

public class CreateSaleQuery {

	
	public static ArrayList<Customer> exe(Connection conn,ArrayList<ArrayList<CheckBoxObject>> salesPersonToCustomer,String patternId) { //with sector and type condition
		ArrayList<Customer> arr = new ArrayList<Customer>();
		ArrayList<String> employeeId = new ArrayList<String>();
		ResultSet res = null; 
		Statement stmt;
		
		int patternID = Integer.parseInt(patternId.substring(1, patternId.length()));
		Calendar cal = Calendar.getInstance();
	    int year = cal.get(cal.YEAR);
	    int month = cal.get(cal.MONTH)+1;
	    int day = cal.get(cal.DAY_OF_MONTH);
		int saleID;
		
			try {
				stmt = conn.createStatement();
				
				res = stmt.executeQuery("SELECT * from ccrm.employee where position='S'; ");
				while(res.next())
				{
					
					employeeId.add(res.getString(1).toString());
				
				}
				
				stmt = conn.createStatement();
				stmt.executeUpdate("INSERT INTO ccrm.marketingsales (marketingPatternID,isActive,startDate) "
						+ "VALUES ("+patternID +",1,'"+year+"-"+month+"-"+day+"');");
				
				res = stmt.executeQuery("SELECT * from ccrm.marketingsales ORDER BY id DESC; ");
				res.next();
				saleID=res.getInt(1);
				
				stmt = conn.createStatement();
				
				
				for (int i=0;i<salesPersonToCustomer.size();i++)
				{
					for (int j=0;j<salesPersonToCustomer.get(0).size();j++)
					{
						if (salesPersonToCustomer.get(i).get(j).getSlected()==1)
						{
						stmt.executeUpdate("INSERT INTO ccrm.marketingsalesaddressing (marketingSaleID,empID,customerID,addressed) "
								+ "VALUES ("+saleID +",'"+employeeId.get(i) +"',"+salesPersonToCustomer.get(i).get(j).getName().substring(0, 9) +",0);");
					
						}
					}
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		return arr;
	}
	
	
}
